﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class SPDAO
    {
        public List<SanPhamDTO> LayDSSanPham()
        {
            string sql = "SELECT * FROM SanPham";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> danhSach = new List<SanPhamDTO>();
           
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
                danhSach.Add(spDTO);
            }
            conn.Close();
            return danhSach;
        }

        public string LayTenSP(int maSP)
        {
            string sql = " SELECT TenSP FROM SanPham WHERE MaSP = " + maSP;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            string kq = (string)cmd.ExecuteScalar();
            return kq;

        }

        public void CapNhatThemSanPham(int maSP,int soLuong)
        {
            string sql = "UPDATE SanPham SET SLTon = SLTon + " + soLuong + " WHERE MaSP = " + maSP;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void capnhatsodu(int masp,int soluong,DateTime dt)
        {
            dt = dt.AddMonths(1);
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "UPDATE SoDU set SoDu=SoDu + "+ soluong +" where MaSP=" + masp.ToString() + " and Month(SoDu.Ngay)=" +dt.Month.ToString() + " and Year(SoDu.Ngay)=" + dt.Year.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);            
            cmd.ExecuteNonQuery();
            conn.Close();
            cmd.Dispose();
        }

    }
}
